package DAO.DAOImple;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import util.DBTool;
import util.Encoding;
import model.AccountQuestion;
import model.Group;
import model.HeadImg;
import model.MeetRecord;
import model.MeetRecordMessage;
import model.Page;
import model.Post;
import model.User;
import DAO.MeetRecordDAO;

public class MeetRecordImple implements MeetRecordDAO {
	
	/** 增加例会记录
	 * @param user  headImg
	 * @return result // 0-上传失败 1-成功��½�ɹ�
	 */
	public int addMeetRecord(MeetRecord meetRecord) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		int result = 0; 
		
		Post post = null;
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "insert into T_MeetingRecord" + 
				"(meetDate, groupId, schoolYear, semester, weekNum, startTime, endTime, location, organizer, recorder, joiner, leaver," + 
				" absence, hearer, mainContent, followThing)" + 
				"values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setString(1, (new SimpleDateFormat( "yyyy-MM-dd").format(meetRecord.getMeetDate())));
			pstmt.setInt(2, meetRecord.getGroup().getId());
			pstmt.setInt(3, meetRecord.getSchoolYear());
			pstmt.setInt(4, meetRecord.getSemester());
			pstmt.setInt(5, meetRecord.getWeekNum());
			pstmt.setString(6, meetRecord.getStartTime());
			pstmt.setString(7, meetRecord.getEndTime());
			pstmt.setString(8, meetRecord.getLocation());
			pstmt.setInt(9, meetRecord.getOrganizer().getId());
			pstmt.setInt(10, meetRecord.getRecorder().getId());
			pstmt.setString(11, meetRecord.getJoiner());
			pstmt.setString(12, meetRecord.getLeaver());
			pstmt.setString(13, meetRecord.getAbsence());
			pstmt.setString(14, meetRecord.getHearer());
			pstmt.setString(15, meetRecord.getMainContent());
			pstmt.setString(16, meetRecord.getFollowThing());
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	/** 获得例会记录列表
	 * 
	 * @return result // meetRecords 例会记录列表
	 */
	public List<MeetRecord> getMeetRecodList(String querySql, Page page) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		List<MeetRecord> meetRecords = new ArrayList<MeetRecord>();
		MeetRecord meetRecord = null;
		
		String sql = "select m.*,g.groupName, u1.id, u1.name, u2.id, u2.name from " 
			+ "T_MeetingRecord as m,T_Group as g,T_User as u1, T_User as u2" 
			+ " where m.groupID = g.id and m.organizer = u1.id and m.recorder = u2.id and m.state = 0" + querySql + " order by m.id"+
				" limit ?, ?";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, page.getStartRow());
			pstmt.setInt(2, page.getPageSize());
			rs = pstmt.executeQuery();

			while(rs.next()) {
				meetRecord = new MeetRecord();
				meetRecord.setId(rs.getInt("m.id"));
				meetRecord.setMeetDate(rs.getDate("m.meetDate"));
				
				meetRecord.setGroup(new Group());
				meetRecord.getGroup().setGroupName(rs.getString("g.groupName"));

				meetRecord.setSchoolYear(rs.getInt("m.schoolYear"));
				meetRecord.setSemester(rs.getInt("m.semester"));
				meetRecord.setWeekNum(rs.getInt("m.weekNum"));
				
				meetRecord.setOrganizer(new User());
				meetRecord.getOrganizer().setId(rs.getInt("u1.id"));
				meetRecord.getOrganizer().setName(rs.getString("u1.name"));
				
				meetRecord.setRecorder(new User());
				meetRecord.getRecorder().setId(rs.getInt("u2.id"));
				meetRecord.getRecorder().setName(rs.getString("u2.name"));
				
				meetRecords.add(meetRecord);
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return meetRecords;
	}

	public MeetRecord getMeetRecord(int meetRecordId) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		MeetRecord meetRecord = null;
		String sql = "select m.*,g.groupName,g.id, u1.id, u1.name, u2.id, u2.name from " 
			+ "T_MeetingRecord as m,T_Group as g,T_User as u1, T_User as u2" 
			+ " where m.groupID = g.id and m.organizer = u1.id and m.recorder = u2.id and m.state = 0 and m.id = ? order by m.id";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, meetRecordId);
			rs = pstmt.executeQuery();

			if(rs.next()) {
				meetRecord = new MeetRecord();
				meetRecord.setId(rs.getInt("m.id"));
				meetRecord.setMeetDate(rs.getDate("m.meetDate"));
				
				meetRecord.setGroup(new Group());
				meetRecord.getGroup().setId(rs.getInt("g.id"));
				meetRecord.getGroup().setGroupName(rs.getString("g.groupName"));

				meetRecord.setSchoolYear(rs.getInt("m.schoolYear"));
				meetRecord.setSemester(rs.getInt("m.semester"));
				meetRecord.setWeekNum(rs.getInt("m.weekNum"));
				meetRecord.setStartTime(rs.getString("m.startTime"));
				meetRecord.setEndTime(rs.getString("m.endTime"));
				meetRecord.setLocation(rs.getString("m.location"));
				
				meetRecord.setOrganizer(new User());
				meetRecord.getOrganizer().setId(rs.getInt("u1.id"));
				meetRecord.getOrganizer().setName(rs.getString("u1.name"));
				
				meetRecord.setRecorder(new User());
				meetRecord.getRecorder().setId(rs.getInt("u2.id"));
				meetRecord.getRecorder().setName(rs.getString("u2.name"));
				
				meetRecord.setJoiner(rs.getString("m.joiner"));
				meetRecord.setLeaver(rs.getString("m.leaver"));
				meetRecord.setAbsence(rs.getString("m.absence"));
				meetRecord.setHearer(rs.getString("hearer"));
				meetRecord.setMainContent(rs.getString("m.mainContent"));
				meetRecord.setFollowThing(rs.getString("followThing"));
				meetRecord.setState(rs.getInt("m.state"));
				
			}else {
				
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return meetRecord;
	}

	public List<MeetRecordMessage> getMRMessageList(int id, Page page) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		List<MeetRecordMessage> mRMessages = new ArrayList<MeetRecordMessage>();
		MeetRecordMessage mRMessage = null;
		
		String sql = "select mm.*, u.id, u.name from T_MeetRecordMessage as mm, T_User as u " + 
					"where mm.userId = u.id and mm.meetRecordId = ? and mm.state = 0 order by mm.id desc limit ?,?";
		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, id);
			pstmt.setInt(2, page.getStartRow());
			pstmt.setInt(3, page.getPageSize());
			rs = pstmt.executeQuery();

			while(rs.next()) {
				mRMessage = new MeetRecordMessage();
				mRMessage.setId(rs.getInt("mm.id"));
				
				mRMessage.setMeetRecord(new MeetRecord());
				mRMessage.getMeetRecord().setId(rs.getInt("mm.meetRecordId"));
				
				mRMessage.setUser(new User());
				mRMessage.getUser().setId(rs.getInt("u.id"));
				mRMessage.getUser().setName(rs.getString("u.name"));
				
				mRMessage.setMessageTime(rs.getTimestamp("mm.messageTime"));
				mRMessage.setContent(rs.getString("mm.content"));
				mRMessage.setState(rs.getInt("mm.state"));
				
				mRMessages.add(mRMessage);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return mRMessages;
	}

	public int addMRMessage(MeetRecordMessage message) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		int result = 0;
		
		String sql = "insert into T_MeetRecordMessage(meetRecordId,userId,messageTime,content) values(?,?,?,?)";
		
		try{
			 pstmt = dbt.getPstmt(sql);
			 pstmt.setInt(1, message.getMeetRecord().getId());
			 pstmt.setInt(2, message.getUser().getId());
			 pstmt.setString(3, (new SimpleDateFormat( "yyyy-MM-dd hh:mm:ss").format(new Date())));
			 pstmt.setString(4, message.getContent());
			 
			 int urs = pstmt.executeUpdate();
			 if(urs != 0) {
				result = 1;
			 }
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public int deleteMRMessage(int id) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		int result = 0;
		
		String sql = "delete from T_MeetRecordMessage where id = ?";
		
		try{
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, id);
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public int modifyMeetRecord(MeetRecord meetRecord) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		int result = 0; 
		
		Post post = null;
		
		DBTool dbt = DBTool.getDBTool();
		String sql = "update T_MeetingRecord set " + 
				"meetDate = ?, groupId = ?, schoolYear = ?, semester = ?, weekNum = ?, startTime = ?, endTime = ?, location = ?, " + 
				"organizer = ?, recorder = ?, joiner = ?, leaver = ?," + 
				" absence = ?, hearer = ?, mainContent = ?, followThing = ?" +
				"where id = ?";

		try {
			pstmt = dbt.getPstmt(sql);
			pstmt.setString(1, (new SimpleDateFormat( "yyyy-MM-dd").format(meetRecord.getMeetDate())));
			pstmt.setInt(2, meetRecord.getGroup().getId());
			pstmt.setInt(3, meetRecord.getSchoolYear());
			pstmt.setInt(4, meetRecord.getSemester());
			pstmt.setInt(5, meetRecord.getWeekNum());
			pstmt.setString(6, meetRecord.getStartTime());
			pstmt.setString(7, meetRecord.getEndTime());
			pstmt.setString(8, meetRecord.getLocation());
			pstmt.setInt(9, meetRecord.getOrganizer().getId());
			pstmt.setInt(10, meetRecord.getRecorder().getId());
			pstmt.setString(11, meetRecord.getJoiner());
			pstmt.setString(12, meetRecord.getLeaver());
			pstmt.setString(13, meetRecord.getAbsence());
			pstmt.setString(14, meetRecord.getHearer());
			pstmt.setString(15, meetRecord.getMainContent());
			pstmt.setString(16, meetRecord.getFollowThing());
			pstmt.setInt(17, meetRecord.getId());
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

	public int deleteMeetRecord(int id) {
		ResultSet rs = null;
		PreparedStatement pstmt = null;
		DBTool dbt = DBTool.getDBTool();
		
		int result = 0;
		
		String sql = "update T_MeetingRecord set state = 1 where id = ?";
		
		try{
			pstmt = dbt.getPstmt(sql);
			pstmt.setInt(1, id);
			int urs = pstmt.executeUpdate();
			if(urs != 0) {
				result = 1;
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}finally {
			dbt.close(rs);
			dbt.close(pstmt);
			dbt.closeConn();
		}
		return result;
	}

}
